CREATE VIEW IADEGOSTERGE_YIL_AY AS
SELECT     dbo.VE_DELIVERY.COMPANYID, dbo.VE_DELIVERY.CPERIODID, YEAR(dbo.VE_DELIVERY.TRANSDATE) AS YIL, DATEPART(MM, dbo.VE_DELIVERY.TRANSDATE) 
                      AS AY, (CASE WHEN DATEPART(MM, VE_DELIVERY.TRANSDATE) = 1 THEN 'OCAK' WHEN DATEPART(MM, VE_DELIVERY.TRANSDATE) 
                      = 2 THEN 'UBAT' WHEN DATEPART(MM, VE_DELIVERY.TRANSDATE) = 3 THEN 'MART' WHEN DATEPART(MM, VE_DELIVERY.TRANSDATE) 
                      = 4 THEN 'NSAN' WHEN DATEPART(MM, VE_DELIVERY.TRANSDATE) = 5 THEN 'MAYIS' WHEN DATEPART(MM, VE_DELIVERY.TRANSDATE) 
                      = 6 THEN 'HAZRAN' WHEN DATEPART(MM, VE_DELIVERY.TRANSDATE) = 7 THEN 'TEMMUZ' WHEN DATEPART(MM, VE_DELIVERY.TRANSDATE) 
                      = 8 THEN 'AUSTOS' WHEN DATEPART(MM, VE_DELIVERY.TRANSDATE) = 9 THEN 'EYLL' WHEN DATEPART(MM, VE_DELIVERY.TRANSDATE) 
                      = 10 THEN 'EKM' WHEN DATEPART(MM, VE_DELIVERY.TRANSDATE) = 11 THEN 'KASIM' WHEN DATEPART(MM, VE_DELIVERY.TRANSDATE) 
                      = 12 THEN 'ARALIK' END) AS AYLAR, SUM(dbo.VE_DELIVERYITEM.QUANTITY) AS SATISMIKTAR,
                          (SELECT     SUM(B.QUANTITY) AS Expr1
                            FROM          dbo.VE_DELIVERY AS A INNER JOIN
                                                   dbo.VE_DELIVERYITEM AS B ON A.COMPANYID = B.COMPANYID AND A.CPERIODID = B.CPERIODID AND A.RECEIPTID = B.RECEIPTID
                            WHERE      (A.COMPANYID = dbo.VE_DELIVERY.COMPANYID) AND (A.ACTID = dbo.VE_DELIVERY.ACTID) AND (A.CPERIODID = dbo.VE_DELIVERY.CPERIODID) AND 
                                                   (A.TRANSTYPE = 120) AND (YEAR(A.TRANSDATE) = YEAR(dbo.VE_DELIVERY.TRANSDATE)) AND (DATEPART(MM, A.TRANSDATE) = DATEPART(MM, 
                                                   dbo.VE_DELIVERY.TRANSDATE))) AS IADEMIKTAR,
                          (SELECT     SUM(QUANTITYLOSS) AS KAYIP
                            FROM          dbo.VE_PQCUSTERRORFORM
                            WHERE      (COMPANYID = dbo.VE_DELIVERY.COMPANYID) AND (ACTID = dbo.VE_DELIVERY.ACTID) AND (YEAR(TRANSDATE) 
                                                   = YEAR(dbo.VE_DELIVERY.TRANSDATE)) AND (DATEPART(MM, TRANSDATE) = DATEPART(MM, dbo.VE_DELIVERY.TRANSDATE))) AS HATATOPL, 
                      dbo.VE_DELIVERY.ACTNAME, dbo.VE_DELIVERY.ACTID, dbo.VE_ACT.REGION AS HEDEFPPM
FROM         dbo.VE_ACT INNER JOIN
                      dbo.VE_DELIVERY INNER JOIN
                      dbo.VE_DELIVERYITEM ON dbo.VE_DELIVERY.COMPANYID = dbo.VE_DELIVERYITEM.COMPANYID AND 
                      dbo.VE_DELIVERY.CPERIODID = dbo.VE_DELIVERYITEM.CPERIODID AND dbo.VE_DELIVERY.RECEIPTID = dbo.VE_DELIVERYITEM.RECEIPTID ON 
                      dbo.VE_ACT.COMPANYID = dbo.VE_DELIVERY.COMPANYID AND dbo.VE_ACT.ACTID = dbo.VE_DELIVERY.ACTID
WHERE     (dbo.VE_DELIVERY.TRANSTYPE = 102)
GROUP BY dbo.VE_DELIVERY.COMPANYID, dbo.VE_DELIVERY.CPERIODID, YEAR(dbo.VE_DELIVERY.TRANSDATE), DATEPART(MM, dbo.VE_DELIVERY.TRANSDATE), 
                      dbo.VE_DELIVERY.ACTNAME, dbo.VE_DELIVERY.ACTID, dbo.VE_ACT.REGION